package com.gipson.amc.utils.excelprocess;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

/**
 * POI工具类。该文件来自于gipson-ocs项目。
 *
 * @author evan 2017-07-10
 * @version 1.0
 */
public class POIHelper {

	// 声明一个工作薄
	HSSFWorkbook workbook = new HSSFWorkbook();

	/**
	 * 定义单元格
	 *
	 * @param bgColor 背景颜色
	 * @param align   单元格左右对其方式
	 * @param border  是否存在单元格边框
	 * @param font    单元格字体
	 * @return
	 */
	public HSSFCellStyle definedCellStyle(short bgColor,
										  HorizontalAlignment align, boolean border, HSSFFont font) {
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置背景颜色样式
		style.setFillForegroundColor(bgColor);
		// 是否存在边框
		if (border) {
			// 设置单元格边框
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			style.setBorderTop(BorderStyle.THIN);
		}
		// 设置单元格左右对其方式
		style.setAlignment(align);
		// 设置单元格上下对其方式
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// 设置单元格文本是否被包装
		style.setWrapText(true);
		// 把字体应用到当前的样式
		style.setFont(font);
		return style;
	}

	/**
	 * 定义单元格字体
	 *
	 * @param bold     是否加粗
	 * @param fontSize 字体大小
	 * @param fontName 字体名称
	 * @return
	 */
	public HSSFFont definedFont(boolean bold, short fontSize, String fontName) {
		HSSFFont font = workbook.createFont();
		font.setBold(bold);
		font.setColor(Font.COLOR_NORMAL);
		font.setFontHeightInPoints(fontSize);
		font.setFontName(fontName);
		return font;
	}

	/**
	 * 定义单元格字体
	 *
	 * @param bold     是否加粗
	 * @param fontSize 字体大小
	 * @param fontName 字体名称
	 * @param color    字体颜色
	 * @return
	 */
	public HSSFFont definedFontAndColor(boolean bold, short fontSize, String fontName, short color) {
		HSSFFont font = workbook.createFont();
		font.setBold(bold);
		font.setColor(Font.COLOR_NORMAL);
		font.setFontHeightInPoints(fontSize);
		font.setFontName(fontName);
		font.setColor(color);
		return font;
	}

	/**
	 * 添加单元格内容 文本格式
	 *
	 * @param style     单元格样式
	 * @param row
	 * @param cellIndex
	 * @param cellValue
	 */
	public void addXlsCell(HSSFCellStyle style, HSSFRow row, int cellIndex,
						   String cellValue) {
		HSSFCell cell = row.createCell(cellIndex);
		cell.setCellType(CellType.STRING);
		cell.setCellValue(cellValue);
		cell.setCellStyle(style);
	}

	public void addXlsCell(HSSFCellStyle style, HSSFRow row, int cellIndex,
						   Integer cellValue) {
		this.addXlsCell(style, row, cellIndex, cellValue.toString());
	}

	/**
	 * 添加单元格内容 货币格式
	 *
	 * @param style
	 * @param row
	 * @param cellIndex
	 * @param cellValue
	 */
	public void addXlsCell(HSSFCellStyle style, HSSFRow row, int cellIndex,
						   Double cellValue) {
		if (cellValue == null || cellValue.equals(0.0)) {
			this.addXlsCell(style, row, cellIndex, this.get2Double(cellValue));
		} else {
			style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
			HSSFCell cell = row.createCell(cellIndex);
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue(cellValue);
			cell.setCellStyle(style);
		}
	}

	/**
	 * Double类型转标准货币类型
	 *
	 * @param value
	 * @return
	 */
	private String get2Double(Double value) {
		if (value == null) {
			value = 0.0;
		}
		BigDecimal money = new BigDecimal(value);
		if (money.equals(new BigDecimal(0))) {
			return "-";
		} else {
			DecimalFormat df = new DecimalFormat("###,##0.00");
			return df.format(money.doubleValue());
		}
	}

	/**
	 * 设置工作簿
	 *
	 * @param sheetName
	 * @return
	 */
	public HSSFSheet getHSSFSheet(String sheetName) {
		HSSFSheet sheet = workbook.getSheet(sheetName);
		if (sheet == null) {
			sheet = workbook.createSheet(sheetName);
		}
		return sheet;
	}

	public OutputStream save(OutputStream out) throws IOException {
		workbook.write(out);
		out.flush();
		return out;
	}


	/**
	 * 将poiHelper中的信息输出到response.outputStream中。
	 *
	 * @param response
	 * @author cjw 2019-07-17
	 */
	public void printToOutputStream(HttpServletResponse response, String fileName) {

		ServletOutputStream servletOutputStream = null;
		try {
			response.reset();
			// 设置下载文件的类型为任意类型
			response.setContentType("application/OCTET-STREAM;charset=UTF-8");
			// 设置下载文件名称
			fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
			// 获得输出网络流
			servletOutputStream = response.getOutputStream();
			//将信息输出到servletOutputStream。
			workbook.write(servletOutputStream);
			servletOutputStream.flush();
			servletOutputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (servletOutputStream != null) {
				try {
					servletOutputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 创建表头信息，并返回rowIndex。通用方法。
	 *
	 * @param
	 * @return
	 * @author cjw 2019-08-07
	 */
	public int exportHeadCommon(HSSFSheet sheet, List<String> columnHead, Map<Integer, Integer> map_columnWidth) {
		HSSFFont titleFont = definedFont(true, (short) 18, "宋体");
		HSSFFont headFont = definedFont(true, (short) 16, "宋体");
		HSSFCellStyle titleStyle = definedCellStyle(Font.COLOR_NORMAL, HorizontalAlignment.CENTER, true, titleFont);
		HSSFCellStyle headStyle = definedCellStyle(Font.COLOR_NORMAL, HorizontalAlignment.CENTER, true, headFont);
		HSSFRow row = null;
		int rowIndex = 0;
		/*
		row = sheet.createRow(rowIndex);
		row.setHeight((short) 1000);
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, EmployeeTempBind.COLUMN_HEAD.size() - 1));
		poiHelper.addXlsCell(titleStyle, row, 0, "临时卡绑定表");
		rowIndex++;
		*/
		row = sheet.createRow(rowIndex);
		row.setHeight((short) 500);
		// 添加表头行信息
		int cellIndex = 0;
		for (String columnName : columnHead) {
			sheet.setColumnWidth(cellIndex, map_columnWidth.get(cellIndex));
			addXlsCell(headStyle, row, cellIndex, columnName);
			cellIndex++;
		}
		return rowIndex;
	}

}
